﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;



namespace Conexion
{
    public class clsConexion
    {
        private string usr;
        private string pwd;

        public string BD = "datos_001", ip = "192.168.0.72";
        public SqlConnection conectar = new SqlConnection();



        private string _contraseña;
        private string _usuario;

        public string Contraseña
        {
            get { return _contraseña; }
            set { _contraseña = value; }
        }
        public string Usuario
        {
            get { return _usuario; }
            set { _usuario = value; }
        }

        public bool ConectarBD()
        {
            usr = "sa";
            pwd = "hermosillo";
            if (conectar.State == ConnectionState.Open)
            {
                return true;
            }
            else
            {

                conectar.ConnectionString = "Database=" + BD + ";Data Source=" + ip + "; User Id=" + usr + ";Password=" + pwd + ";App=RMA;Connect Timeout=300;";
                try
                {
                    conectar.Open();

                    return true;
                }
                catch (SqlException ex) { return false; }
            }

        }
        public DataTable Datatable_standart(string procedimiento, List<clsParametros> parametros)
        {
            DataTable Dataset = new DataTable();
            ConectarBD();
            SqlDataAdapter DataAdapter = new SqlDataAdapter();
            SqlCommand comand = new SqlCommand();
            comand.Connection = conectar;
            comand.CommandType = CommandType.StoredProcedure;
            comand.CommandText = procedimiento;
            comand.CommandTimeout = 100;
            if (parametros != null)
            {
                foreach (clsParametros p in parametros)
                {
                    comand.Parameters.AddWithValue(p.Nombre_parametro, p.Valor);
                }
            }
            DataAdapter.SelectCommand = comand;
            DataAdapter.Fill(Dataset);
            conectar.Close();
            return Dataset;
        }
        public DataTable Datatable_standart(string procedimiento)
        {
            DataTable Dataset = new DataTable();
            ConectarBD();
            SqlDataAdapter DataAdapter = new SqlDataAdapter();
            SqlCommand comand = new SqlCommand();
            comand.Connection = conectar;
            comand.CommandType = CommandType.StoredProcedure;
            comand.CommandText = procedimiento;
            comand.CommandTimeout = 10;
            
            DataAdapter.SelectCommand = comand;
            DataAdapter.Fill(Dataset);
            conectar.Close();
            return Dataset;
        }
        public DataTable Datatable_standart_texto(string procedimiento)
        {
            DataTable Dataset = new DataTable();
            ConectarBD();
            SqlDataAdapter DataAdapter = new SqlDataAdapter();
            SqlCommand comand = new SqlCommand();
            comand.Connection = conectar;
            comand.CommandType = CommandType.Text;
            comand.CommandText = procedimiento;
            comand.CommandTimeout = 10;

            DataAdapter.SelectCommand = comand;
            DataAdapter.Fill(Dataset);
            conectar.Close();
            return Dataset;
        }
        public bool Procedure_standart(string procedimiento, List<clsParametros> parametros)
        {
            ConectarBD();
            SqlCommand comand = new SqlCommand();
            comand.Connection = conectar;
            comand.CommandType = CommandType.StoredProcedure;
            comand.CommandText = procedimiento;
            comand.CommandTimeout = 10;
            foreach (clsParametros p in parametros)
            {
                if (p.Image == null)
                {
                    comand.Parameters.AddWithValue(p.Nombre_parametro, p.Valor);
                }
                else
                {
                    comand.Parameters.AddWithValue(p.Nombre_parametro, p.Image);
                }
                
            }
            comand.ExecuteNonQuery();
            conectar.Close();

            return true;

        }
        public int Procedure_standartReturn(string procedimiento, List<clsParametros> parametros)
        {
            int Folio = 0;
            ConectarBD();
            SqlCommand comand = new SqlCommand();
            comand.Connection = conectar;
            comand.CommandType = CommandType.StoredProcedure;
            comand.CommandText = procedimiento;
            comand.CommandTimeout = 10;
            foreach (clsParametros p in parametros)
            {
                comand.Parameters.AddWithValue(p.Nombre_parametro, p.Valor);
            }
            Folio = int.Parse(comand.ExecuteScalar().ToString());
            conectar.Close();

            return Folio;

        }
        public bool entrar(string sql)
        {
            bool existe = false;
            SqlDataReader dr = null;
            SqlCommand cm = new SqlCommand();
            if (ConectarBD() == true)
            {
                cm.CommandType = CommandType.Text;
                cm.CommandText = sql;
                cm.Connection = conectar;
                dr = cm.ExecuteReader();

                if (dr.Read())
                {
                    existe = true;
                }
            }
            dr.Close();
            cm.Connection.Close();
            return existe;
        }
        public object Scalar(string procedimiento, List<clsParametros> parametros)
        {
            object Resultado;
            ConectarBD();
            SqlCommand comand = new SqlCommand();
            comand.Connection = conectar;
            comand.CommandType = CommandType.StoredProcedure;
            comand.CommandText = procedimiento;
            comand.CommandTimeout = 10;

            foreach (clsParametros p in parametros)
            {
                comand.Parameters.AddWithValue(p.Nombre_parametro, p.Valor);
            }
            try
            {
                Resultado = comand.ExecuteScalar();
                conectar.Close();
                return Resultado;
            }
            catch (SqlException error)
            {
                Resultado = "Error" + error;
                conectar.Close();
                return Resultado;
            }
        }






    }
}
